package com.southwind.utils;


import java.sql.*;

/**
 * Created by chenhs on 2018/6/26.
 */
public class JdbcTools {

    static String url = "jdbc:mysql://127.0.0.1:3306/test";//10.225.78.24 :sql服务器IP
    static String name = "root";
    static String password = "admiN123!";

    /**
     * 注册驱动jdbc
     */
    static {
        try {
            Class clazz = Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据库连接
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, name, password);
    }


    /**
     * 关闭数据库
     */
    public static void close(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }


    /**
     * 插入数据
     */
    public static void insertData() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //2.建立连接
            conn = getConnection();
            //3.执行sql语句
            String sql = "insert into t_pipi values(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, "1");
            pstmt.setObject(2, "pipi");
            pstmt.setObject(3, "92");
            pstmt.setObject(4, "62");
            pstmt.setObject(5, "82");
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            close(conn, pstmt, null);
        }
    }


    /**
     * 删除数据
     */
    public static void deleteData() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "delete from t_pipi where name='pipi1' ";
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, null);
        }
    }


    /**
     * 更新数据
     */
    public static void updateData() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update t_pipi set name='pipi1' where id = 1  ";
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, null);
        }
    }


    /**
     * 查询数据
     */
    public static void selectData() throws Exception {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = (Connection) DriverManager.getConnection(url, name, password);
            st = conn.createStatement();
            rs = st.executeQuery("select * from t_pipi");
            while (rs.next()) {
                System.out.println("id=" + rs.getObject("id") + "---> name=" + rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            st.close();
            conn.close();
        }
    }

    /**
     * 批量插入数据
     */
    public static void insertBatch() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcTools.getConnection();
            pstmt = conn.prepareStatement("insert into t_pipi values(?,?,?,?,?,?)");
            for (int i = 0; i < 10; i++) {
                pstmt.setObject(1, "33" + i);
                pstmt.setObject(2, "xiao33");
                pstmt.setObject(3, 23);
                pstmt.setObject(4, 23);
                pstmt.setObject(5, 23);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.close(conn, pstmt, null);
        }
    }
}
